IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_CreateUser]')
              AND TYPE IN (N'P' ,N'PC')
   )
    DROP PROCEDURE [dbo].[cms_sp_CreateUser]
GO
CREATE PROCEDURE [dbo].[cms_sp_CreateUser]
    @UserId INT = NULL OUTPUT,
    @SiteId INT = NULL,
    @SiteGroupId INT = NULL,
    @LoginName NVARCHAR(150),
    @UserEmail NVARCHAR(250),
    @Firstname NVARCHAR(250),
    @Lastname NVARCHAR(250) = NULL,
    @PasswordHash NVARCHAR(150),
    @PasswordSalt NVARCHAR(150) = NULL,
    @IsSystem BIT,
    @CreatedBy INT,
    @DateCreated DATETIME
AS
    IF @IsSystem IS NULL
    BEGIN
        SET @IsSystem = 0
    END
    
    INSERT INTO [dbo].[Users]
      (
        [SiteId]
       ,[SiteGroupId]
       ,[LoginName]
       ,[UserEmail]
       ,[Firstname]
       ,[Lastname]
       ,[IsEnabled]
       ,[IsSystem]
       ,[CreatedBy]
       ,[DateCreated]
      )
    VALUES
      (
        @SiteId
       ,@SiteGroupId
       ,@LoginName
       ,@UserEmail
       ,@Firstname
       ,@Lastname
       ,1
       ,@IsSystem
       ,@CreatedBy
       ,@DateCreated
      )
    SET @UserId = SCOPE_IDENTITY()
    INSERT INTO [dbo].[UserPasswords]
      (
        [UserId]
       ,[PasswordHash]
       ,[PasswordSalt]
       ,[IsLatestRevision]
       ,[IsInitial]
       ,[CreatedBy]
       ,[DateCreated]
      )
    VALUES
      (
        @UserId
       ,@PasswordHash
       ,@PasswordSalt
       ,1
       ,1
       ,@CreatedBy
       ,@DateCreated
      )
    RETURN @UserId
GO